select
  CONCAT(BLO.nprojnr, CONCAT('-',CONCAT(BLO.nkassanr,CONCAT('-',blo.ntransnr)))),
  EIN.nkartennr as wtp,
  BLO.nkundenkartentypnr as c_grille,
  GRI.szname as n_grille,
  BLO.nperstypnr as c_personne,
  PER.szname as n_personne,
  BLO.feinzeltarif as prix,
  DUR.szname as duree,
  KON.szname as caisse,
  PDV.szname as pdv,
  poo.szname  as POOL,
  TRANS.nzutrnr as rm,
  TRANS.dtverwdat as date_passage
  
  from 
 tabkassastornosatz EIN

  inner join tabkassablocksatz BLO
  on CONCAT(EIN.nprojnr,CONCAT('-',CONCAT(EIN.nKassaNr,CONCAT('-',EIN.ntransnr)))) 
  =  CONCAT(BLO.nprojnr, CONCAT('-',CONCAT(BLO.nkassanr,CONCAT('-',blo.ntransnr))))
  
  inner join
    tablesertrans TRANS 
  on CONCAT(EIN.nprojnr, CONCAT('-',CONCAT(EIN.nkassanr,CONCAT('-',EIN.ntransnr))))=
  CONCAT(TRANS.nverkprojnr,CONCAT('-',CONCAT(TRANS.nkassanr,CONCAT('-',TRANS.nseriennr)))) 
  

   
  LEFT JOIN Tabkundenkartentypdef DUR 
  ON BLO.NKUNDENKARTENTYPNR = DUR.NKUNDENKARTENTYPNR
  
  LEFT JOIN tabtarifblattdef GRI 
  on BLO.ntarifblattnr = GRI.ntarifblattnr
  
  LEFT JOIN Tabkassakonf KON
  ON (EIN.NKASSANR = KON.NKASSANR AND EIN.NPROJNR = KON.NPROJNR)
  
  LEFT JOIN Tabstandortedef PDV
  ON KON.NSTANDORTNR = PDV.nstandortnr
  
  LEFT JOIN tabperstypdef PER           
  ON BLO.NPERSTYPNR = PER.NPERSTYPNR
  
  INNER JOIN tabpooldef POO
ON BLO.npoolnr = poo.npoolnr


  where
  BLO.nkassanr = 40
  and
  BLO.DTAUSGABEDAT between  '01/12/11' and '31/12/11' 
  and
  BLO.npoolnr is not null
